<?

//Data loading
$title='DETAIL UMUR PIUTANG';
//if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
//else
 $perush = 'PT. MASAJI PRAYASA CARGO';

$page   = 'PAGE';

$DEFINE_FONT=0;
$pdf=new PDFReport('L',"mm","Letter");
$pdf->AddPage();
$Result = GridList ();
if ($Result) $pdf->GridTable($Result);
$pdf->Output();

function GetFromDate () {
//  list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
//  return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
    list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
    return array($LMonth,$LYear,$LNMonth);
}

function FlushCustomer ($TableName, $tmp) {
    global $DBConnection;

    if ($tmp) $TEMPORARY = "TEMPORARY";

    //$SQL = "DROP TABLE IF EXISTS `$TableName`;";
    //$DBConnection->dbc->query($SQL);

    $SQL= "CREATE $TEMPORARY TABLE `$TableName` (
              `cucode` varchar(20) NOT NULL default '',
              `cunama` varchar(50) default NULL,
              `top` int(4) default NULL

              )";
    $DBConnection->dbc->query($SQL);
    $SQL = "INSERT INTO $DBName.`$TableName` SELECT cucode,cuname,top FROM tbldebtur ";
    $DBConnection->dbc->query($SQL);


    return true;
}

function GridList () {
            global $DBConnection;
            $TEMP = "TEMPORARY";

            FlushCustomer ("aiscustomer", 1);
            //$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

            list($day,$month,$year) = explode("/",GetParam("agingDate",""));
            $current_date = "$year-$month-$day";

            //$SQL = "DROP TABLE IF EXISTS `ais_piutang_terhutang`";
            //$DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terhutang` (
                      `skada` varchar(8) default NULL,
                      `ktrasl` varchar(5) default NULL,
                      `tglbkt` date,
                      `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                      `debtur` varchar(16) default NULL,
                      `debnam` varchar(32) default NULL,
                      `uraian` text default NULL,
                      `curr` varchar(4) default NULL,
                      `jumlah` varchar(32) default NULL,
                      `cr_term` tinyint(4)default NULL,
                      `aging` varchar(4)default NULL
                    ) TYPE=MyISAM;
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terhutang
                    SELECT SKADA, KTRASL, TGLBKT, NOBKT,NOBKTA, DEBTUR, DEBNAM, URAIAN, CURR, JUMLAH,    TOP,
          IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
          IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
          IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))
                    FROM ais_piutang
                    LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
                    WHERE srt='1' AND ais_piutang.tglbkt<='$current_date' ";

          if (GetParam("office",""))   $SQL .= " AND ais_piutang.ktrasl = '".GetParam("office","")."' ";
          if (GetParam("curr",""))   $SQL .= " AND ais_piutang.curr = '".GetParam("curr","")."' ";
          if (GetParam("customerID","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("customerID","")."' ";
          $SQL .= " order by nobkt";

                        //print $SQL;die();

            $DBConnection->dbc->query($SQL);

            //$SQL = "DROP TABLE IF EXISTS `ais_piutang_terbayar`";
            //$DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terbayar` (
                      `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                      `bayar` varchar(32) default NULL
                    ) TYPE=MyISAM;
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terbayar
                    SELECT NOBKT, NOBKTA, SUM(JUMLAH)
                    FROM ais_piutang
          LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
                    WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' ";
          if (GetParam("office",""))   $SQL .= " AND ais_piutang.ktrasl = '".GetParam("office","")."' ";
          if (GetParam("curr",""))   $SQL .= " AND ais_piutang.curr = '".GetParam("curr","")."' ";
          if (GetParam("customerID","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("customerID","")."' ";

          $SQL .= " group by nobkta order by nobkta";
                        //print $SQL;die();
            $DBConnection->dbc->query($SQL);


            //$SQL = "DROP TABLE IF EXISTS `ais_piutang_remain`";
            //$DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_remain` (
                      `skada` varchar(8) default NULL,
                      `ktrasl` varchar(5) default NULL,
                      `tglbkt` date,
                      `nobkt` varchar(16) default NULL,
                      `debtur` varchar(16) default NULL,
                      `debnam` varchar(32) default NULL,
                      `uraian` text default NULL,
                      `curr` varchar(4) default NULL,
                      `cr_term` tinyint(4)default NULL,
                      `jumlahcurr` varchar(32)default NULL,
                      `jumlah30` varchar(32)default NULL,
                      `jumlah60` varchar(32)default NULL,
                      `jumlah60g` varchar(32)default NULL
                    ) TYPE=MyISAM;
                   ";
            $DBConnection->dbc->query($SQL);


                        $SQL = "
                                        INSERT INTO ais_piutang_remain
                                        SELECT
                                        ais_piutang_terhutang.skada,
                                        ais_piutang_terhutang.ktrasl,
                                        ais_piutang_terhutang.tglbkt,
                                        ais_piutang_terhutang.nobkt,
                                        ais_piutang_terhutang.debtur,
                                        ais_piutang_terhutang.debnam,
                                        ais_piutang_terhutang.uraian,
                                        ais_piutang_terhutang.curr,
                                        ifnull(ais_piutang_terhutang.cr_term, 0) AS top,
                                        if(ais_piutang_terhutang.aging='0', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlahcurr,
                                        if(ais_piutang_terhutang.aging='30', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah30,
                                        if(ais_piutang_terhutang.aging='60', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah60,
                                        if(ais_piutang_terhutang.aging='60g', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah90
                                        FROM
                                        ais_piutang_terhutang
                                        LEFT OUTER JOIN ais_piutang_terbayar ON (ais_piutang_terhutang.nobkt = ais_piutang_terbayar.nobkta)
                                        WHERE
                                          (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR (ais_piutang_terhutang.jumlah-ais_piutang_terbayar.bayar) <> 0
                                        ORDER BY debtur,nobkt
                        ";
            $DBConnection->dbc->query($SQL);

                   $SQL = "
                    SELECT ktrasl, nobkt, tglbkt, debtur, debnam, uraian, cr_term as top,
                    ".setMinusORPlus('jumlahcurr').",".setMinusORPlus('jumlah30').",
                    ".setMinusORPlus('jumlah60').",".setMinusORPlus('jumlah60g')."
                    FROM ais_piutang_remain
                   ";
                   $SQL .= "
                    ORDER BY debtur
                   ";
            return $DBConnection->dbc->get_results($SQL,ARRAY_A);

}
function setMinusORPlus($field){
    global $DBConnection;
    $ResultFilter = $DBConnection->dbc->get_row("SELECT araging FROM tblprofile");
    $filter = explode(' ',$ResultFilter->araging);
    $SQL = "(CASE WHEN (";
    $arrFilter = array();
    foreach($filter as $f)
     $arrFilter[] = "skada LIKE '$f%'";
    $SQL .= implode(' OR ',$arrFilter).") THEN $field ELSE -$field END) $field";
    return $SQL;
}


class PDFReport extends PDF {

    function Header() {
        global $title,$perush,$page,$DEFINE_FONT;
        if ($DEFINE_FONT==0) {
            $this->AddFont('Arial','','arialn.php');
            $DEFINE_FONT=1;
        }

        list($Day,$Month,$Year,$NMonth) = GetFromDate();
        list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);

          $this->SetFont('Arial','',10);
          $this->Cell(260,5,$perush,0,1,'C',0);
          $this->Cell(260,5,$title,0,1,'C',0);
          $this->Cell(260,5,"AS OF: ".GetParam("agingDate",""),0,1,'C',0);
          $this->Cell(260,5,$page." ".$this->PageNo(),0,1,'R',0);
          $this->Line(10,25,270,25);
          $this->Ln();
          $this->Ln();
          $this->Ln();
        //Header

        //Column widths
        $w=array(80,25,10,25,30,30,30,30);
        $this->SetFont('Arial','',8);
        $this->Cell($w[0],5,"DEBTUR",1,0,'C',0);
        $this->Cell($w[1],5,"NOBKT",1,0,'C',0);
        $this->Cell($w[2],5,"CR",1,0,'C',0);
            $this->Cell($w[3],5,"CURRENT",1,0,'C',0);
            $this->Cell($w[4],5,"1-30",1,0,'C',0);
            $this->Cell($w[5],5,"31-60",1,0,'C',0);
            $this->Cell($w[6],5,">60",1,0,'C',0);
            $this->Cell($w[7],5,"TOTAL",1,0,'C',0);
        $this->Ln();
    }

    function GridTable($MyResult) {
        //Column widths
        $w=array(80,25,10,25,30,30,30,30);

        //Data
        foreach($MyResult as $row)
        {
            $row_total  = $row["jumlahcurr"]+$row["jumlah30"]+$row["jumlah60"]+$row["jumlah60g"];
            $customer   = $row["debtur"].' - '.$row["debnam"];

            if (($lastdebnam<>$row["debtur"]) && (strlen($lastdebnam)>0)) {
                $this->Cell($w[0]+$w[1]+$w[2],5,"SUB TOTAL:",1,0,'R',0);
            $this->Cell($w[3],5,number_format($subtotalcurr,2),'LRBT',0,'R');
                $this->Cell($w[4],5,number_format($subtotal30,2),'LRBT',0,'R');
            $this->Cell($w[5],5,number_format($subtotal60,2),'LRBT',0,'R');
            $this->Cell($w[6],5,number_format($subtotal60g,2),'LRBT',0,'R');
            $this->Cell($w[7],5,number_format($subtotalall,2),'LRBT',0,'R');
                $this->Ln();
                $subtotalcurr = 0;
                $subtotal30   = 0;
                $subtotal60   = 0;
                $subtotal60g  = 0;
                $subtotalall  = 0;
            }

            $subtotalcurr = $subtotalcurr  + $row["jumlahcurr"];
            $subtotal30   = $subtotal30    + $row["jumlah30"];
            $subtotal60   = $subtotal60    + $row["jumlah60"];
            $subtotal60g  = $subtotal60g   + $row["jumlah60g"];
            $subtotalall  = $subtotalcurr + $subtotal30 + $subtotal60 + $subtotal60g;

            if (($lastdebnam<>$row["debtur"]) && (strlen($row["debtur"]))) $this->Cell($w[0],5,$customer,'LR');
                        else $this->Cell($w[0],5,"",'LR');

            $this->Cell($w[1],5,$row["nobkt"],'LR');
                        $this->Cell($w[2],5,$row["top"],'LR',0,'R');
            $this->Cell($w[3],5,number_format($row["jumlahcurr"],2),'LR',0,'R');
                        $this->Cell($w[4],5,number_format($row["jumlah30"],2),'LR',0,'R');
            $this->Cell($w[5],5,number_format($row["jumlah60"],2),'LR',0,'R');
            $this->Cell($w[6],5,number_format($row["jumlah60g"],2),'LR',0,'R');
            $this->Cell($w[7],5,number_format($row_total,2),'LR',0,'R');
                        $this->Ln();
                        //$lastdebnam = $row["debnam"];
            $lastdebnam = $row["debtur"];

                        $total_cur  = $total_cur + $row["jumlahcurr"];
                        $total_30   = $total_30 + $row["jumlah30"];
                        $total_60   = $total_60 + $row["jumlah60"];
                        $total_60g  = $total_60g + $row["jumlah60g"];

        }

                        $this->Cell($w[0]+$w[1]+$w[2],5,"SUB TOTAL:",1,0,'R',0);
            $this->Cell($w[3],5,number_format($subtotalcurr,2),'LRBT',0,'R');
                        $this->Cell($w[4],5,number_format($subtotal30,2),'LRBT',0,'R');
            $this->Cell($w[5],5,number_format($subtotal60,2),'LRBT',0,'R');
            $this->Cell($w[6],5,number_format($subtotal60g,2),'LRBT',0,'R');
            $this->Cell($w[7],5,number_format($subtotalall,2),'LRBT',0,'R');
                        $this->Ln();
            $subtotalcurr = 0;
            $subtotal30   = 0;
            $subtotal60   = 0;
            $subtotal60g  = 0;
            $subtotalall  = 0;


        $total_row = $total_cur + $total_30 + $total_60 + $total_60g;
        $this->Cell($w[0]+$w[1]+$w[2],5,"TOTAL:",1,0,'R',0);
        $this->Cell($w[3],5,number_format($total_cur,2),1,0,'R',0);
        $this->Cell($w[4],5,number_format($total_30,2),1,0,'R',0);
        $this->Cell($w[5],5,number_format($total_60,2),1,0,'R',0);
            $this->Cell($w[6],5,number_format($total_60g,2),1,0,'R',0);
            $this->Cell($w[7],5,number_format($total_row,2),1,0,'R',0);
        $this->Ln();

        //Closure line
    }

    function Footer() {
        //Closure line
        $this->Cell(260,0,'','T');
    }

}

?>
